package com.apestech.framework.sql.lib;

import java.util.ArrayList;

import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.sql.MultiDataBase;
import com.apestech.framework.util.StringFuncUtil;

public class SelectSql extends StringFuncUtil {
	
	private ArrayList<Integer> selList;
	private ArrayList<Integer> fromList;
	private int fromStart=-1;
	private int fromEnd=-1;
	private int whereStart=-1;
	private int whereEnd=-1;
	private int orderStart=-1;
	private int orderEnd=-1;
	private int groupStart=-1;
	private int groupEnd=-1;
	private String sql;
	private String operSql;
	
	public SelectSql(String sql) throws Exception{
		this.sql=sql;
		this.operSql=sql.toLowerCase();
		this.selList=indexOfList(this.operSql, " select ", "select ", "(select ", "\tselect ", " select\t", "\rselect ", " select\r", "\nselect ", " select\n");
		this.fromList=indexOfList(this.operSql, " from ","\tfrom "," from\t","\rfrom "," from\r","\nfrom "," from\n","from(");
		String error=check();
		if(!"".equals(error)){
			throw new Exception(error);
		}
		JSONObject mapping=getMapping(this.selList, " from ", this.operSql);
		getSqlStructure(mapping);
	}
	
	/**
	 * 返回空串代表无错，有错返回错误信息，不会判断全表操作
	 * @return
	 */
	public String check(){
		return check(true);
	}
	
	/**
	 * 返回空串代表无错，有错返回错误信息，不会判断全表操作
	 * @return
	 */
	public String check(boolean alldata){
		try {
			checkSelect(true);
		} catch (Exception e) {
			return e.getMessage();
		} 
		return "";
	}
	
	/**
	 * 查询SQL有效性检查
	 * @param alldata
	 * @throws Exception
	 */
	public void checkSelect(boolean alldata) throws Exception{
		int sel=this.selList.size();
		int from=this.fromList.size();
		if(sel!=from){
			throw new Exception(getMessage("SelectSql_errorSelSql","请输入正确的查询SQL:select[({0})]和from[({1})]对应数量必须相同!", sel, from));
		}
		if(!alldata){
			if(this.operSql.indexOf(" where ")<0){
				throw new Exception(getMessage("SelectSql_errorSelSqlAll","不允许全表查询!"));
			}
		}
	}
	
	/**
	 * 获取主查询语句中的select子串
	 * @return
	 * @throws Exception
	 */
	public String getSelSel() throws Exception{
		return this.sql.substring(0, this.fromStart-6);
	}
	
	/**
	 * 获取主查询语句中的from子串
	 * @return
	 * @throws Exception
	 */
	public String getSelFrom() throws Exception{
		return this.sql.substring(this.fromStart-6, this.fromEnd);
	}
	
	/**
	 * 获取主查询语句中的from子串中的表名
	 * @return
	 * @throws Exception
	 */
	public String getSelFromTable() throws Exception{
		return this.sql.substring(this.fromStart, this.fromEnd);
	}
	
	/**
	 * 获取主查询语句中的where子串
	 * @return
	 * @throws Exception
	 */
	public String getSelWhere() throws Exception{
		return this.sql.substring(this.whereStart, this.whereEnd);
	}
	
	/**
	 * 获取主查询语句中的order子串
	 * @return
	 * @throws Exception
	 */
	public String getSelOrder(){
		if(this.orderStart<0){
			return "";
		} else {
			return this.sql.substring(this.orderStart, this.orderEnd);
		}
	}
	
	/**
	 * 获取主查询语句中的group子串
	 * @return
	 * @throws Exception
	 */
	public String getSelGroup(){
		if(this.groupStart<0){
			return "";
		} else {
			return this.sql.substring(this.groupStart, this.groupEnd);
		}
	}

	/**
	 * 获取查询语句from开始的位置
	 * @param text
	 * @param pos
	 * @return
	 */
	private String getSelFromStartPos(String text, int pos){
		int ilast=getLastPos(text, " from ");
		String from=text.substring(ilast+6, text.length());
		this.fromStart=pos+ilast+6;
		return from;
	}
	
	/**
	 * 获取查询语句from结束的位置
	 * @param text
	 * @param pos
	 * @return
	 */
	private String getSelFromEndPos(String text, int pos){
		int end=text.indexOf(" where ");
		if(end>=0){
			end=getLastPos(text, " where ");
		}
		if(end<0){
			end=getLastPos(text," order ");
			if(end<0){
				end=getLastPos(text," group ");
				if(end<0){
					end=text.length();
				}
			}
		} 
		text=text.substring(0,end);
		this.fromEnd=pos+end;
		this.whereStart=this.fromEnd;
		this.whereEnd=this.sql.length();
		return text;
	}
	
	/**
	 * 获取查询语句中order by 和 group by位置
	 * @param text
	 */
	private void getSelOtherPos(String text){
		text=text.toLowerCase();
		if(!"".equals(text.trim())){
			String sub="";
			int pos=text.indexOf(" order ");
			if(pos>=0){
				pos=getLastPos(text, " order ");
				sub=text.substring(pos, text.length());
				if(sub.indexOf(")")<0){  //防止获取的子串是WHERE中的子查询部分
					this.orderStart=this.fromEnd+pos;
					this.orderEnd=this.fromEnd+pos+sub.length();
				}
			}
			pos=text.indexOf(" group ");
			if(pos>=0){
				pos=getLastPos(text, " group ");
				sub=text.substring(pos, text.length());
				if(sub.indexOf(")")<0){  //防止获取的子串是WHERE中的子查询部分
					if(this.orderEnd>0){
						this.orderEnd=this.fromEnd+pos;
					}
					this.groupStart=this.fromEnd+pos;
					this.groupEnd=this.fromEnd+pos+sub.length();
				}
			}
				
		}
		if(this.orderStart>=0){
			this.whereEnd=this.orderStart;
		} else if(this.groupStart>=0){
			this.whereEnd=this.groupStart;
		}
	}
	
	/**
	 * 获取整个SELECT语句的基础结构各关键字初始位置
	 * @param mapping
	 * @return
	 * @throws Exception
	 */
	private String getSqlStructure(JSONObject mapping) throws Exception{
		String from=null;
		int kindex=mapping.getIntValue("index");
		JSONArray list=mapping.getJSONArray("list");
		int len=list.size();
		list.get(kindex);
		for(int i=0; i<len; i++){
			JSONObject map=(JSONObject) list.get(i);
			String sub=map.getString("text");
			if(i==kindex){
				from=getSelFromStartPos(sub, map.getIntValue("st"));
				int pos=from.indexOf("(");
				if(pos>=0){
					int wpos=from.indexOf(" where ");
					if(wpos<pos && wpos>=0){  //代表（属于WHERE部分
						from=getSelFromEndPos(from, this.fromStart);
					} else {
						int eindex=getWherePos(list, i, len);
						from=from+getFromString(list, i+1, eindex);
					}
					
				} else {
					from=getSelFromEndPos(from, this.fromStart);
				}
				break;
			}
		}
		getSelOtherPos(getSelWhere());
		return from;
	}
	
	/**
	 * 存在FROM中有()情况的余串处理
	 * @param list
	 * @param st
	 * @param en
	 * @return
	 * @throws Exception
	 */
	private String getFromString(JSONArray list, int st, int en) throws Exception{
		String str="";
		for(int i=st; i<=en; i++){
			JSONObject map=(JSONObject) list.get(i);
			String sub=map.getString("text");
			if(i==en){
				sub=getSelFromEndPos(sub, map.getIntValue("st"));
			}	
			str=str+sub;
		}
		return str;
	}
	
	/**
	 * 获取WHERE子串所在位置
	 * @param list
	 * @param index
	 * @param len
	 * @return
	 */
	private int getWherePos(JSONArray list, int index, int len){
		int ipos=-1;
		for(int i=index; i<len; i++){
			JSONObject map=(JSONObject) list.get(i);
			String sub=map.getString("text");
			if(countString(sub, " where ")>1){
				ipos=i;
				break;
			}
		}
		if(ipos==-1){
			ipos=len-1;
		}
		return ipos;
	}
	
	/**
	 * 获取查询SQL结构
	 * @return
	 * @throws Exception
	 */
	public JSONObject getSqlStructure() throws Exception{
		JSONObject map=new JSONObject();
		map.put("select", getSelSel());
		map.put("from", getSelFrom());
		map.put("where", getSelWhere());
		map.put("order", getSelOrder());
		map.put("group", getSelGroup());
		map.put("table", getSelFromTable());
		return map;
	}
}
